#!usr/bin/env python  
# -*- coding:utf-8 _*-
""" 
@author:robot
@file: tap.py 
@version:
@time: 2022/03/17 
"""
# !usr/bin/env python
# -*- coding:utf-8 _*-
from pprint import pprint

""" 
@author:robot
@file: connector_demo.py 
@version:
@time: 2022/03/17 
"""
import inspect
import os
import sys

sys.path.append(os.getcwd())
import fire

from TapClientConnector import *
import db_info_cloud as _


def get_classes(arg):
    classes = {}
    cls_members = inspect.getmembers(arg, inspect.isclass)
    for name, _ in cls_members:
        classes.update({name: _})
    return classes


class TapConnectorPythonClient:

    def __init__(self, source_type='', name='', sql='', table=''):
        self.sql = sql
        self.table = table
        self.source_type = source_type
        self.name = name

    @staticmethod
    def show_db_type():
        pprint(get_classes(_))

    def show_member(self):
        class_info = dir(get_classes(_).get(self.source_type))
        tmp = []
        for i in class_info:
            if '__' not in i:
                tmp.append(i)
        pprint(tmp)

    def show_attr(self):
        pprint(getattr(get_classes(_).get(self.source_type), self.name))

    def query(self):
        """
        查询语句结果获取
        :return:
        """
        db_info = getattr(get_classes(_).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        return connector.tapclient_connector.query(self.sql)

    def common_query(self):
        db_info = getattr(get_classes(_).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        select = connector.tapclient_connector.generate_select(table=self.table)
        return connector.tapclient_connector.query(select)

    def common_count(self):
        db_info = getattr(get_classes(_).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        count = connector.tapclient_connector.generate_select(column='count(*)', table=self.table)
        return connector.tapclient_connector.query(count)

    def common_drop(self):
        db_info = getattr(get_classes(_).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        drop = connector.tapclient_connector.generate_drop(table=self.table)
        return connector.tapclient_connector.exec(drop)

    def drop_all_table(self):
        db_info = getattr(get_classes(_).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        table_list = connector.tapclient_connector.show_tables()
        import asyncio
        async_run = asyncio.get_event_loop().run_until_complete
        async_run(self._drop_all_table_(connector, table_list))

    @staticmethod
    async def _drop_all_table_(connector, table_list):
        for table in table_list:
            drop_operate = connector.tapclient_connector.generate_drop(table)
            connector.tapclient_connector.exec(drop_operate)

    def drop_table_by_key(self):
        db_info = getattr(get_classes(_).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        table_list = connector.tapclient_connector.show_tables()
        import asyncio
        async_run = asyncio.get_event_loop().run_until_complete
        async_run(self._drop_table_by_key(connector, table_list, self.sql))

    @staticmethod
    async def _drop_table_by_key(connector, table_list, key):
        for table in table_list:
            if key in table:
                drop_operate = connector.tapclient_connector.generate_drop(table)
                connector.tapclient_connector.exec(drop_operate)

    def show_dbs(self):
        """
        查询数据库包含库
        :return:
        """
        db_info = getattr(get_classes(_).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        return connector.tapclient_connector.show_dbs()

    def show_tables(self):
        """
        查询数据库表包含表
        :return:
        """
        db_info = getattr(get_classes(_).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        return connector.tapclient_connector.show_tables()

    def execute(self):
        """
        执行语句
        :return:
        """
        db_info = getattr(get_classes(_).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        return connector.tapclient_connector.exec(self.sql)

    def pg_create(self):
        db_info = getattr(get_classes(_).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        old = connector.tapclient_connector.connector.isolation_level
        connector.tapclient_connector.connector.set_isolation_level(0)
        connector.tapclient_connector.exec(self.sql)
        connector.tapclient_connector.connector.set_isolation_level(old)

    def ms_db_cdc(self):
        db_info = getattr(get_classes(_).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        return connector.tapclient_connector.special_execute("""EXEC sys.sp_cdc_enable_db""")

    def ms_table_cdc(self):
        db_info = getattr(get_classes(_).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        sql = connector.tapclient_connector.generate_table_cdc(self.table)
        return connector.tapclient_connector.special_execute(sql)

    def ms_table_all_cdc(self):
        db_info = getattr(get_classes(_).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        table_list = connector.tapclient_connector.show_tables()
        for i in table_list:
            try:
                sql = connector.tapclient_connector.generate_table_cdc(i)
                connector.tapclient_connector.special_execute(sql)
            except:
                pass

    def ms_special_execute(self):
        db_info = getattr(get_classes(_).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        return connector.tapclient_connector.special_execute(self.sql)

    def ms_query_db_cdc(self):
        db_info = getattr(get_classes(_).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        sql = connector.tapclient_connector.generate_db_cdc_query()
        return connector.tapclient_connector.query(sql)

    def ms_query_all_table_cdc(self):
        db_info = getattr(get_classes(_).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        table_list = connector.tapclient_connector.show_tables()
        tmp = []
        for i in table_list:
            sql = f"SELECT [name],is_tracked_by_cdc FROM sys.tables WHERE [name] = N'{i}'"
            result = connector.tapclient_connector.query(sql)
            if not result[1]:
                tmp.append(result)
        print('no cdc table')
        for j in tmp:
            print(j)

    @staticmethod
    def help():
        print("""
        # python connector 命令行查询工具
        # 已支持的数据源   
        - mysql √   
        - oracle √  
        - mongodb √
        - db2 √   
        - postgres √
        - sqlserver √
        - gbase-8s ×
        - sybase ase ×
        - gaussdb200 √
        - elasticsearch √
        - redis √
        - kafka √
        - mariadb √
        - mysql pxc √
        - dameng √,执行机环境未搭建
        - hive ×
        - tcp_udp ×
        - mq ×
        - hbase ×
        - kudu ×
        - greenplum √
        - tidb √
        - hana √
        - clickhouse √
        - kundb √
        - adb_postgres √
        - adb_mysql √
        - vika ×
        - hazelcast_cloud_cluster ×
    
        tap.py

        需要引入一个数据库配置模块,我默认是这样引入的
        
        import db_info as _
        
        你可以去替换db_info模块来配置自己的数据库配置
        
        db_info模块的应类似这样
        
        class Mysql:
            database_type = "mysql"
        
            mysql_source_test = {
                "name": "mysql_source_test",
                "database_type": database_type,
                "database_host": "47.115.163.10",
                "database_port": "3306",
                "database_name": "source",
                "database_username": "root",
                "plain_password": "zsj@520wm",
                "connection_type": source_and_target
            }
        
            auto_mysql_source = {
                "name": "auto_mysql_source",
                "database_type": database_type,
                "database_host": "47.115.163.10",
                "database_port": "3306",
                "database_name": "source",
                "database_username": "root",
                "plain_password": "zsj@520wm",
                "connection_type": source_and_target
            }
        
        
        引入的模块有许多的class，对应输入参数的source_type
        class的属性，对应输入参数的name
        
        通过--source_type=className --name=attrName 来获取数据源
        
        --sql指定需要执行的语句，execute为执行，query为查询，更多请查看下面的示例
        ```
        
        ## 功能：
        
        支持query、count、show_tables、show_dbs命令
        
        ### 示例
        
        - 查询: 会返回结果，注意不要查询数据量大的表，会卡死
        
        ```cmd
        python 命令行脚本 --source_type=Mysql --name=auto_mysql --sql="select * from my2" query
        
        python 命令行脚本 --source_type=Mysql --name=auto_mysql --sql="select count(*) from my2" query
        
        python 命令行脚本 --source_type=Mysql --name=auto_mysql show_dbs
        
        python 命令行脚本 --source_type=Mysql --name=auto_mysql show_tables
        ```    
        
        - 执行：不返回结果,会打印执行信息,使用query也一样，只是返回结果为空
        
        ```cmd
        python 命令行脚本 --source_type=Mysql --name=auto_mysql --sql="create table fire(column int)" execute|query
        
        python 命令行脚本--source_type=Mysql --name=auto_mysql --sql="drop table testClient"  execute|query
        ```
        
        ## special
        
        1.sqlserver、postgres、oracle注意小写需要用""引住
        
        2.mongodb的sql是这样定义的
        
        - query
        
        ```cmd
        ("find", (collection_name:str, filter:dict, show:dict))
        ("count", (collection_name:str, filter))
        
        python TapClientConnector/tapconnector_python_client/connector_demo.py 
        --source_type=MongoDB --name=auto_mongo_source --sql="("find", ('mg2',{}))" query
        
        python TapClientConnector/tapconnector_python_client/connector_demo.py --source_type=MongoDB 
        --name=auto_mongo_source --sql="("count", ('mg2',{}))" query
        
        ```
        
        - execute
        
        ```cmd
        ("insert_one", (collection_name:str, document:dict))
        ("insert_many", (collection_name:str, document:list))
        
        python TapClientConnector/tapconnector_python_client/connector_demo.py 
        --source_type=MongoDB --name=auto_mongo_source --sql="("insert_one", ('fire',{'test':'test'}))" execute
        
        python TapClientConnector/tapconnector_python_client/connector_demo.py 
        --source_type=MongoDB --name=auto_mongo_source --sql="("insert_many", ('fire',[{'test':'test'}]))" execute
        
        
        ("update_one", (collection_name:str, filter:dict, update:ditc))
        ("update_many", (collection_name:str, filter:dict, update:dict))
        
        python TapClientConnector/tapconnector_python_client/connector_demo.py --source_type=MongoDB 
        --name=auto_mongo_source --sql="("update_one", ('fire',{'test':'test'}, {'test':'update'}))" execute
        
        python TapClientConnector/tapconnector_python_client/connector_demo.py --source_type=MongoDB 
        --name=auto_mongo_source --sql="("update_many", ('fire',{'test':'test'}, {'test':'update'}))" execute
        
        ("delete_one", (collection_name:str, filter:dict))
        ("delete_many", (collection_name:str, filter:dict))
        
        python TapClientConnector/tapconnector_python_client/connector_demo.py --source_type=MongoDB 
        --name=auto_mongo_source --sql="("delete_one", ('fire',{'test':'test'}))" execute
        
        python TapClientConnector/tapconnector_python_client/connector_demo.py --source_type=MongoDB 
        --name=auto_mongo_source --sql="("delete_many", ('fire',{'test':'test'}))" execute
        
        
        ("drop_db", (collection_name:str,))
        ("drop_table", (collection_name:str,))
        ```
        
        - 更多数据源执行sql说明待完善
            - es的使用方式:支持查询索引、已存在的索引、删除索引
            ```shell
            python376 tap.py --source_type=ES --name=ES_189_target  --sql="c_i_mg2es" query
            python376 tap.py --source_type=ES --name=ES_189_target  --sql="c_i_mg2es" show_tables
            python376 tap.py --source_type=ES --name=ES_189_target  --sql="{'operate': 'drop', 'index': 'index_name'}" execute
            ```
            - redis的使用方式:支持查键值对，已存在的键值对，删除键值对|只支持通过tapdata传输的数据结构
        
        ```shell
          python376 tap.py --source_type=Redis --name=auto_redis_target_00 --sql="" show_tables
          python376 tap.py --source_type=Redis --name=auto_redis_target_00 --sql="['1']" query
          python376 tap.py --source_type=Redis --name=auto_redis_target_00 --sql="['14title1', '1']" query
          python376 tap.py --source_type=Redis --name=auto_redis_target_00 --sql="{'operate': 'drop': 'table': ['14title1', '1']}" execute
        
        
        ```
        
            - kafka的使用方式:只topic的查询，已存在的topic查询，删除topic
        
        ```shell
          python376 tap.py --source_type=Kafka --name=kafka_183_all_topic --sql="" show_tables
          python376 tap.py --source_type=Kafka --name=kafka_183_all_topic --sql="CLAIM" query
          python376 tap.py --source_type=Kafka --name=kafka_183_all_topic --sql="{'operate': 'drop': 'topic': ['14title1', '1']}" execute
          新增插入删除
          python376 tap.py --source_type=Kafka --name=kafka_183_all_topic --sql="{'operate': 'insert', 
                                                                                  'topic': 'test', 
                                                                                   topic_data:{'test':'test'}" execute
          python376 tap.py --source_type=Kafka --name=kafka_183_all_topic --sql="{'operate': 'update', 
                                                                                  'topic': 'test', 
                                                                                   topic_data:{'test':'test'}" execute
          python376 tap.py --source_type=Kafka --name=kafka_183_all_topic --sql="{'operate': 'delete', 
                                                                                  'topic': 'test', 
                                                                                   topic_data:{'test':'test'}" execute                                                                                                                                                      
    ```
""")


if __name__ == '__main__':
    print("查询数据库种类：python tap.py show_db_type")
    print("查询某种数据库下的成员: python tap.py --source_type=Mysql show_member")
    print("查询查询某个数据库的连接信息 python tap.py --source_type=Mysql --name=auto_mysql show_attr")
    print("命令格式：python tap.py --source_type=--name= --sql=\"\" query|execute|show_dbs|show_tables")
    print("快速查询：python tap.py --source_type=--name= --table=\"test\" common_query| common_count| common_drop")
    print("sqlserver 更多执行：python tap.py --source_type=--name= --sql="" ms_special_execute")
    print("开启sqlserver 表cdc：python tap.py --source_type=--name= --table=\"test\" ms_table_cdc")
    print("sqlserver 开启所有表的cdc：python tap.py --source_type=--name=  ms_table_all_cdc")
    print("开启sqlserver 库cdc：python tap.py --source_type=--name=  ms_db_cdc")
    print("sqlserver 查询所有表的cdc：python tap.py --source_type=--name=  ms_query_all_table_cdc")
    print("sqlserver 查询当前库的cdc：python tap.py --source_type=--name=  ms_query_db_cdc")
    print("postgres 建库 查询当前库的cdc：python tap.py --source_type=--name=  pg_create")
    print("删除源所有表：python tap.py --source_type=--name=  drop_all_table")
    print('删除源所有表：python tap.py --source_type=--name=  --sql="关键字" drop_table_by_key')
    print('python376 tap.py help: 查看更多帮助信息')
    fire.Fire(TapConnectorPythonClient)
